const express = require("express");
const router = express.Router();
const dayjs = require("dayjs");
const utc = require("dayjs/plugin/utc");
const timezone = require("dayjs/plugin/timezone");

dayjs.extend(utc);
dayjs.extend(timezone);
let connectDAO = require("./connect.js");

// 登陆
router.get("/login", function (req, res) {
  const { PhoneNumber, Password } = req.query;
  const sql = `SELECT * FROM Officers WHERE PhoneNumber = '${PhoneNumber}' AND Password = '${Password}';`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        data: records[0],
      });
    } else {
      res.send({
        code: 201,
        msg: "账号或密码不正确",
      });
    }
  });
});

// 注册
router.post("/register", function (req, res) {
  const { Name, Department, PhoneNumber } = req.body;
  const sql = `INSERT INTO Officers (Name, Gender,Rank,Department,PhoneNumber, Permission,Password,HireDate) VALUES 
  ('${Name}', 'Male', '警员', '${
    Department || "派出所"
  }', '${PhoneNumber}', 'user','${PhoneNumber.slice(-6)}', '${dayjs().format(
    "YYYY-MM-DD"
  )}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 获取值班记录
router.get("/get_duty_records", function (req, res) {
  const { OfficerID, IsSure } = req.query;
  const sql = `SELECT * FROM DutyRoster ${
    OfficerID
      ? `WHERE OfficerID = '${OfficerID}'${
          IsSure !== undefined ? " AND IsSure = 0" : ""
        }`
      : ""
  }`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 新增值班记录
router.post("/add_duty", function (req, res) {
  const { OfficerID, DutyDate, OfficerName } = req.body;
  const sql = `INSERT INTO DutyRoster (OfficerID, DutyDate,OfficerName,IsSure) VALUES 
  ('${OfficerID}', '${DutyDate}', '${OfficerName}',0)`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 确认值班记录
router.put("/confirm_duty", function (req, res) {
  const { DutyID } = req.body;
  const sql = `UPDATE DutyRoster SET IsSure = 1 WHERE DutyID = '${DutyID}'`;
  connectDAO.queryDataBySQL(sql).then(() => {
    res.send({ code: 200, msg: "Success" });
  });
});

// 打卡上岗
router.post("/sign_on", function (req, res) {
  const { OfficerID, CheckInTime, Location } = req.body;
  const sql = `INSERT INTO Attendance (OfficerID, CheckInTime, Location,Status,ScheduleID) VALUES 
  ('${OfficerID}', '${CheckInTime}', '${Location}', '正常', 1)`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 打卡记录
router.get("/get_sign_on_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT l.*,o.Name AS OfficerName FROM Attendance l JOIN Officers o ON l.OfficerID = o.OfficerID ${
    OfficerID ? ` WHERE o.OfficerID = '${OfficerID}'` : ""
  } ORDER BY l.CheckInTime DESC`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 获取人员在岗记录
router.get("/get_person_on_duty_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `
  SELECT 
  o.*,
  future_duty.DutyDate,
  future_duty.IsSure,
  latest_attendance.Location AS AttendanceLocation
FROM 
  Officers o
LEFT JOIN (
  -- 获取每个警员最近的未来排班记录
  SELECT 
      d.*
  FROM 
      DutyRoster d
  INNER JOIN (
      SELECT 
          OfficerID, 
          MIN(DutyDate) AS NearestFutureDate
      FROM 
          DutyRoster
      WHERE 
          DutyDate >= CURDATE()  -- 只考虑未来排班
      GROUP BY 
          OfficerID
  ) nearest ON d.OfficerID = nearest.OfficerID AND d.DutyDate = nearest.NearestFutureDate
) future_duty ON o.OfficerID = future_duty.OfficerID
LEFT JOIN (
  -- 获取每个警员最新的考勤记录
  SELECT 
      a.*
  FROM 
      Attendance a
  INNER JOIN (
      SELECT 
          OfficerID, 
          MAX(CheckInTime) AS MaxCheckIn
      FROM 
          Attendance
      GROUP BY 
          OfficerID
  ) latest ON a.OfficerID = latest.OfficerID AND a.CheckInTime = latest.MaxCheckIn
) latest_attendance ON o.OfficerID = latest_attendance.OfficerID
WHERE 
  o.Permission != 'admin' OR o.Permission IS NULL
ORDER BY 
  future_duty.DutyDate Desc;  -- 按未来排班日期升序排列（越近的排越前）`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 获取officers
router.get("/get_officers_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT * FROM Officers ${
    OfficerID ? "WHERE OfficerID = " + OfficerID : ""
  }`;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          records,
          total: records.length,
        });
      } else {
        res.send({
          code: 200,
          msg: "no data",
          records,
        });
      }
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

module.exports = router;
